package bid.dao.impl;



import bid.dao.UserRoleDao;
import bid.entity.UserRole;
import bid.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserRoleDaoImpl implements UserRoleDao {
    @Override
    public int add(UserRole ur) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "insert into userrole" +
                "(name,remark)";
        sql += "values(?,?)";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1, ur.getName());
            st.setString(2, ur.getRemark());
            int r = st.executeUpdate();
            return r;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int del(long id) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "delete FROM userrole where id = ?";

//        sql += " where id = ?";
        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符

            st.setLong(1, id);
            int r = st.executeUpdate();
            return r;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int update(UserRole ur) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "update userrole set " +
                "name = ?,remark = ?";
        sql += " where id = ?";
        System.out.println(ur.getId());
        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符
            st.setString(1, ur.getName());
            st.setString(2, ur.getRemark());
            st.setLong(3, ur.getId());
            int r = st.executeUpdate();
            System.out.println(st.toString());
            return r;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public List<UserRole> findAll() {
        String sql = "select * from userrole";
        List<UserRole> users = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数

            rs = st.executeQuery();
            while (rs.next()) {
                UserRole role = new UserRole();
                role.setId(rs.getLong("id"));
                role.setName(rs.getString("name"));
                role.setRemark(rs.getString("remark"));
                users.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return users;
    }

    @Override
    public List<UserRole> search(int pageNo, int pageSize) {
        String sql = "select * from userrole limit ?,?";
        List<UserRole> users = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            st.setInt(1,(pageNo-1)*pageSize);
            st.setInt(2,pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                UserRole role = new UserRole();
                role.setId(rs.getLong("id"));
                role.setName(rs.getString("name"));
                role.setRemark(rs.getString("remark"));
                users.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return users;
    }

    @Override
    public UserRole get(long id) {
        String sql = "select * from userrole where id=?";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();
            if (rs.next()) {
                UserRole ur = new UserRole();
                ur.setId(rs.getLong("id"));
                ur.setName(rs.getString("name"));
                ur.setRemark(rs.getString("remark"));
                //....
                return ur;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return null;
    }
}
